# -*- coding: utf-8 -*-
# @Time     : 2021/10/11 19:14
# @Author   : JustFly
# @File     : main.py
# @Software : PyCharm

import requests
import json
import time
import pymysql
import traceback
from selenium import webdriver


def get_conn():
    """
    :return: 连接, 游标
    """
    # 创建连接
    conn = pymysql.connect(host="127.0.0.1",
                           user="root",
                           password="123456",
                           db="covid_2019",
                           )
    # 创建游标
    cursor = conn.cursor()

    return conn, cursor

def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()

def get_baidu_hot():
    """
    :return: 返回百度热搜前30中的28个
    """

    option = webdriver.FirefoxOptions()
    option.add_argument("--headless")  # 隐藏浏览器弹窗
    option.add_argument("--no-send")

    browser = webdriver.Firefox()
    browser.get('https://top.baidu.com/board?tab=realtime')
    r = browser.find_elements_by_xpath('//*[@id="sanRoot"]/main/div[2]/div/div[2]')  # F12 选中元素右键 copy 得到 Xpath

    hot_data = [i.text.split("\n") for i in r]

    hot_list = []
    n = 0

    for e in hot_data[0]:
        if e == '热' or e == '新':
            hot_data[0].remove(e)
        else:
            n += 1
            if n % 5 == 0:
                hot_list.append(hot_data[0][
                                n - 5: n])  # ['1', '4992729', '热搜指数', '河北大巴坠河事故致14人遇难', '10月11日，河北石家庄市平山县一辆载51人的大巴车落水。截至12日下午2时40分，最后一名失联人员被找到，已无生命体征... 查看更多>']

    return hot_list

def get_tencent_data():
    """
    :return: list全国汇总数据/日期为主键每日更新 list当日详细数据
    """

    url = "http://view.inews.qq.com/g2/getOnsInfo?name=disease_h5"
    headers = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/93.0.4577.82 Safari/537.36'
    }

    r = requests.get(url, headers)
    res = json.loads(r.text)  # json字符串-->字典
    data_all = json.loads(res['data'])

    update_time = data_all['lastUpdateTime']
    chinaTotal = data_all['chinaTotal']
    chinaAdd = data_all['chinaAdd']

    # 全国汇总历史数据

    ds = update_time.split()[0]  # 构造数据库字段
    confirm = chinaTotal['confirm']
    suspect = chinaTotal['suspect']
    heal = chinaTotal['heal']
    dead = chinaTotal['dead']
    confirm_add = chinaAdd['confirm']
    suspect_add = chinaAdd['suspect']
    heal_add = chinaAdd['heal']
    dead_add = chinaAdd['dead']
    history = [ds, confirm, confirm_add, suspect, suspect_add, heal, heal_add, dead, dead_add]

    # 全国各省市当日详情数据
    details = []

    data_province = data_all['areaTree'][0]['children']  # 中国各省

    for pro_infos in data_province:

        province = pro_infos['name']  # 省

        for city_infos in pro_infos['children']:
            city = city_infos['name']  # 市

            confirm = city_infos['total']['confirm']
            confirm_add = city_infos['today']['confirm']
            heal = city_infos['today']['confirm']
            dead = city_infos['total']['dead']
            details.append([update_time, province, city, confirm, confirm_add, heal, dead])

    return history, details

def update_details():
    """
    更新 deatails 表
    :return:
    """

    cursor = None
    conn = None

    try:
        #li = get_tencent_data()[1]  # 0 是历史数据字典, 1 最新详细数据列表
        li = get_tencent_data()[1]
        conn, cursor = get_conn()

        sql = "insert into details(update_time, province, city, confirm, confirm_add, heal, dead) values(%s, %s, %s, %s, %s, %s, %s)"
        sql_query = 'select %s=(select update_time from details order by id desc limit 1)'  # 对比当前最大时间戳 当前数据时间和最大时间相等返回 1 不等返回 0

        cursor.execute(sql_query, li[0][0])  # 随便取一个元素的时间

        if not cursor.fetchone()[0]:
            print(f"{time.asctime()}开始更新最新数据 !")
            for item in li:
                print(item)
                cursor.execute(sql, item)

            conn.commit()  # 提交事务 update delete insert 操作

            print(f"{time.asctime()}更新最新数据完毕 !")
        else:
            print(f"{time.asctime()}已是最新数据 !")

    except:
        traceback.print_exc()  # 打印异常信息
    finally:
        close_conn(conn, cursor)

def update_history():
    """
    插入历史数据, 第一次运行项目直接插入
    :return:
    """

    cursor = None
    conn = None

    try:
        hlist = get_tencent_data()[0]  # 0 是历史数据字典, 1 最新详细数据列表
        print(f"{time.asctime()}开始插入历史数据 !")
        print(hlist)
        conn, cursor = get_conn()

        sql = "insert into history values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = 'select %s=(select ds from history order by ds desc limit 1)'  # 对比当前最大时间戳 当前数据时间和最大时间相等返回 1 不等返回 0

        cursor.execute(sql_query, time.strftime("%Y-%m-%d 00:00:00"))  # 随便取一个元素的时间

        if not cursor.fetchone()[0]:
            cursor.execute(sql, hlist)

            conn.commit()
            print(f"{time.asctime()}更新历史数据完毕 ！")
        else:
            print(f"{time.asctime()}当前数据已是最新 !")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)

def update_baidu_hot():
    """
    插入百度热搜数据
    :return:
    """

    cursor = None
    conn = None

    hot_list = get_baidu_hot()

    print(f"{time.asctime()}开始更新百度热搜数据 !")
    try:
        conn, cursor = get_conn()
        sql = "insert into bdhot(hotrank, hotscore, title, content) values(%s, %s, %s, %s)"
        for hot in hot_list:

            hotrank = int(hot[0])
            hotscore = int(hot[1])
            title = hot[3]
            content = hot[4]

            tup = (hotrank, hotscore, title, content)
            print(tup)
            cursor.execute(sql, tup)
        conn.commit()
        print(f"{time.asctime()}百度热搜数据更新完成 !")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)



if __name__ == '__main__':
    update_details()
    update_history()
    update_baidu_hot()
    update_history()
    update_baidu_hot()
    update_details()



